﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.ServiceModel;
using System.Web;
using QuizService.beans;
using System.Text.RegularExpressions;
using System.Security.Cryptography;
using System.Text;

namespace QuizService.dao
{
    public class dao
    {
        private string connect_url = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\QuizDB.accdb;Persist Security Info=False;";
        private Dictionary<int, question> Questions = new Dictionary<int, question>();
        private Dictionary<int, genre> Genres = new Dictionary<int, genre>();
        private Dictionary<int, match> Matches = new Dictionary<int, match>();
        private Dictionary<int, user> Users = new Dictionary<int, user>();
        private Dictionary<int, toplist> Toplists = new Dictionary<int, toplist>();

        private string convertPassword(string text, Encoding enc)
        {
            byte[] buffer = enc.GetBytes(text);
            SHA256CryptoServiceProvider crypt = new SHA256CryptoServiceProvider();
            return BitConverter.ToString(crypt.ComputeHash(buffer)).Replace("-", "");
        }


        public bool RegisterUser(user r)
        {

            string patternemail = @"^(([^<>()[\]\\.,;:\s@\""]+"
                        + @"(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@"
                        + @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
                        + @"\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+"
                        + @"[a-zA-Z]{2,}))$";
            Regex checkemail = new Regex(patternemail);

            if (r == null)
                throw new FaultException("Nincs megadva user típus a regisztráláshoz!");
            if (r.Username == null || r.Username.Count() == 0)
                throw new FaultException("Nincs megadva felhasználónév!");
            if (r.Password == null)
                throw new FaultException("Nincs megadva jelszó!");
            if (r.Password.Count() < 4)
                throw new FaultException("A jelszónak legalább 4 karakternek kell lennie");
            if (r.Firstname == null || r.Firstname.Count() == 0)
                throw new FaultException("Nincs megadva vezetéknév!");
            if (r.Lastname == null || r.Lastname.Count() == 0)
                throw new FaultException("Nincs megadva keresztnév!");
            if (r.Emailaddress == null || r.Emailaddress.Count() == 0)
                throw new FaultException("Nincs megadva email cím!");
            if (!checkemail.IsMatch(r.Emailaddress))
                throw new FaultException("Ez nem egy email cím!");

            //TODO: Regisztráció elkészítése
            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();

                string checkuser = "select count(*) from [user] WHERE username = @uname";
                OleDbCommand cu = new OleDbCommand(checkuser, conn);
                cu.Parameters.Add("uname", System.Data.DbType.String).Value = r.Username;
                object res = cu.ExecuteScalar();
                if (System.Convert.ToInt32(res) > 0)
                    throw new FaultException("Ilyen felhasználónév már létezik");


                string CommandText = "insert into [user] (username, firstname, lastname, [password], emailaddress) values (@uname, @fname, @lname, @pwd, @email);";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("uname", System.Data.DbType.String).Value = r.Username;
                c.Parameters.Add("fname", System.Data.DbType.String).Value = r.Firstname;
                c.Parameters.Add("lname", System.Data.DbType.String).Value = r.Lastname;
                c.Parameters.Add("pwd", System.Data.DbType.String).Value = this.convertPassword(r.Password, Encoding.UTF8);
                c.Parameters.Add("email", System.Data.DbType.String).Value = r.Emailaddress;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;

            }
        }

        public bool LogInUser(user r)
        {

            if (r == null)
                throw new FaultException("Nincs a bejelentkezéshez user objektum megadva.");
            if (r.Username == null || r.Username.Count() == 0)
                throw new FaultException("Nincs megadva a felhasználónév!");
            if (r.Password == null || r.Password.Count() == 0) 
                throw new FaultException("Nincs megadva jelszó!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText = "select count(*) from [user] where username=@uname AND [password]=@pwd";
                OleDbCommand command = new OleDbCommand(commandText, conn);

                command.Parameters.Add("uname", System.Data.DbType.String).Value = r.Username;
                command.Parameters.Add("pwd", System.Data.DbType.String).Value = this.convertPassword(r.Password, Encoding.UTF8);


                if ((int)command.ExecuteScalar() == 1)
                    return true;
                else
                    return false;

            }
        }

        public question getRandomQuestion(genre g)
        {

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText, commandTextcount;

                if (g != null)
                {
                    if (g.Genre_id == null)
                        throw new FaultException("Nincs megadva a műfaj id-je.");

                    commandText = "select top 1 * from [question] WHERE [genre] = " + g.Genre_id + " ORDER BY Rnd(TimeValue(Now())*-100000000000*[ID])";
                    commandTextcount = "select count(*) from [question] where [genre] = " + g.Genre_id + "";
                }
                else
                {
                    commandText = "select top 1 * from [question] ORDER BY Rnd(TimeValue(Now())*-100000000000*[ID])";
                    commandTextcount = "select count(*) from [question]";
                }
                question ret = null;
                OleDbCommand command2 = new OleDbCommand(commandTextcount, conn);
                int rows = (int)command2.ExecuteScalar();
                if (rows == 0)
                    return ret;
                else
                {
                    OleDbCommand command = new OleDbCommand(commandText, conn);
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        ret = new question();
                        ret.Question = reader["question"].ToString();
                        ret.A_answer = reader["A_answer"].ToString();
                        ret.B_answer = reader["B_answer"].ToString();
                        ret.C_answer = reader["C_answer"].ToString();
                        ret.D_answer = reader["D_answer"].ToString();
                        ret.Answer = reader["good_answer"].ToString();
                        ret.Question_id = (int)reader["ID"];
                        ret.Sender = (int)reader["sender"];
                    }
                    conn.Close();
                    return ret;
                }
            }
        }

        public bool addQuestion(question q)
        {
            if (q == null)
                throw new FaultException("Nincs megadva kérdés objektum a kérdéshozzáadáshoz.");
            if (q.Sender == null || q.Sender <= 0)
                throw new FaultException("Nincs megadva, hogy ki küldte be!");
            if (q.Question == null || q.Question.Count() == 0)
                throw new FaultException("Nincs megadva a kérdés!");
            if (q.A_answer == null || q.A_answer.Count() == 0)
                throw new FaultException("Nincs megadva az A válasz!");
            if (q.B_answer == null || q.B_answer.Count() == 0)
                throw new FaultException("Nincs megadva a B válasz!");
            if (q.C_answer == null || q.C_answer.Count() == 0)
                throw new FaultException("Nincs megadva a C válasz!");
            if (q.D_answer == null || q.D_answer.Count() == 0)
                throw new FaultException("Nincs megadva a D válasz!");
            if (q.Answer == null || q.Answer.Count() == 0)
                throw new FaultException("Nincs megadva a helyes válasz!");
            if (q.Answer.Count() != 1)
                throw new FaultException("A helyes válasznak egyetlen egy karaktert kell tartalmaznia! (pl. A,B,C,D)");
            if (q.Genre_id == null || q.Genre_id <= 0)
                throw new FaultException("Nincs megadva műfaj");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "insert into [question] (sender, question, A_answer, B_answer, C_answer, D_answer, good_answer, genre)"
                + "values (@sender, @question, @a_answer, @b_answer, @c_answer, @d_answer, @good_answer, @genre);";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("sender", System.Data.DbType.Int32).Value = q.Sender;
                c.Parameters.Add("question", System.Data.DbType.String).Value = q.Question;
                c.Parameters.Add("a_answer", System.Data.DbType.String).Value = q.A_answer;
                c.Parameters.Add("b_answer", System.Data.DbType.String).Value = q.B_answer;
                c.Parameters.Add("c_answer", System.Data.DbType.String).Value = q.C_answer;
                c.Parameters.Add("d_answer", System.Data.DbType.String).Value = q.D_answer;
                c.Parameters.Add("good_answer", System.Data.DbType.String).Value = q.Answer;
                c.Parameters.Add("genre", System.Data.DbType.Int32).Value = q.Genre_id;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool delQuestion(question q)
        {
            if (q == null)
                throw new FaultException("Nincs megadva kérdés objektum a kérdéstörléshez.");
            if (q.Question_id == null || q.Question_id <= 0)
                throw new FaultException("Nincs megadva a kérdés id-ja.");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "DELETE FROM [question] WHERE id = @questionid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("questionid", System.Data.DbType.Int32).Value = q.Question_id;
               
                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool modifyUser(user u)
        {
            string patternemail = @"^(([^<>()[\]\\.,;:\s@\""]+"
            + @"(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@"
            + @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
            + @"\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+"
            + @"[a-zA-Z]{2,}))$";
            Regex checkemail = new Regex(patternemail);

            if (u == null)
                throw new FaultException("Nincs megadva user objektum a usermódosításhoz.");
            if (u.Id == null || u.Id <= 0)
                throw new FaultException("Nincs megadva a módosítandó felhasználó id-ja.");
            if (u.Username == null || u.Username.Count() == 0)
                throw new FaultException("Nincs megadva felhasználónév!");
            if (u.Password == null)
                throw new FaultException("Nincs megadva jelszó!");
            if (u.Password.Count() < 4)
                throw new FaultException("A jelszónak legalább 4 karakternek kell lennie");
            if (u.Firstname == null || u.Firstname.Count() == 0)
                throw new FaultException("Nincs megadva vezetéknév!");
            if (u.Lastname == null || u.Lastname.Count() == 0)
                throw new FaultException("Nincs megadva keresztnév!");
            if (u.Emailaddress == null || u.Emailaddress.Count() == 0)
                throw new FaultException("Nincs megadva email cím!");
            if (!checkemail.IsMatch(u.Emailaddress))
                throw new FaultException("Ez nem egy email cím!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "update [user] SET username = @uname, password = @pwd, firstname = @fname, lastname = @lname, emailaddress= @email WHERE [ID] = @userid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add(new OleDbParameter("uname", u.Username));
                c.Parameters.Add(new OleDbParameter("pwd", this.convertPassword(u.Password,Encoding.UTF8)));
                c.Parameters.Add(new OleDbParameter("fname", u.Firstname));
                c.Parameters.Add(new OleDbParameter("lname", u.Lastname));
                c.Parameters.Add(new OleDbParameter("email", u.Emailaddress));
                c.Parameters.Add(new OleDbParameter("userid", u.Id));


                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool modifyQuestion(question q)
        {
            if (q == null)
                throw new FaultException("Nincs megadva kérdés objektum a kérdésmódosításhoz!");
            if (q.Sender == null || q.Sender <= 0)
                throw new FaultException("Nincs megadva, hogy ki küldte be!");
            if (q.Question == null || q.Question.Count() == 0)
                throw new FaultException("Nincs megadva a kérdés!");
            if (q.A_answer == null || q.A_answer.Count() == 0)
                throw new FaultException("Nincs megadva az A válasz!");
            if (q.B_answer == null || q.B_answer.Count() == 0)
                throw new FaultException("Nincs megadva a B válasz!");
            if (q.C_answer == null || q.C_answer.Count() == 0)
                throw new FaultException("Nincs megadva a C válasz!");
            if (q.D_answer == null || q.D_answer.Count() == 0)
                throw new FaultException("Nincs megadva a D válasz!");
            if (q.Answer == null || q.Answer.Count() == 0)
                throw new FaultException("Nincs megadva a helyes válasz!");
            if (q.Answer.Count() != 1)
                throw new FaultException("A helyes válasznak egyetlen egy karaktert kell tartalmaznia! (pl. A,B,C,D)");
            if (q.Genre_id == null || q.Genre_id <= 0) 
                throw new FaultException("Nincs megadva műfaj!");
            if (q.Question_id == null || q.Question_id <= 0)
                throw new FaultException("Nincs megadva a módosítandó kérdés műfaja!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "update [question] SET sender = @sender, question = @question, A_answer = @a_answer," 
                + " B_answer = @b_answer, C_answer = @c_answer, D_answer = @d_answer," 
                + " good_answer = @good_answer, genre = @genre_id WHERE [ID] = @questionid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add(new OleDbParameter("sender", q.Sender));
                c.Parameters.Add(new OleDbParameter("question", q.Question));
                c.Parameters.Add(new OleDbParameter("a_answer", q.A_answer));
                c.Parameters.Add(new OleDbParameter("b_answer", q.B_answer));
                c.Parameters.Add(new OleDbParameter("c_answer", q.C_answer));
                c.Parameters.Add(new OleDbParameter("d_answer", q.D_answer));
                c.Parameters.Add(new OleDbParameter("good_answer", q.Answer));
                c.Parameters.Add(new OleDbParameter("genre_id", q.Genre_id));
                c.Parameters.Add(new OleDbParameter("questionid", q.Question_id));

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public IEnumerable<question> getAllQuestions(genre g)
        {
            Questions.Clear();

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText, commandTextcount;

                if (g != null)
                {
                    commandText = "select * from [question] WHERE [genre] = " + g.Genre_id + "";
                    commandTextcount = "select count(*) from [question] where [genre] = " + g.Genre_id + "";
                }
                else
                {
                    commandText = "select * from [question]";
                    commandTextcount = "select count(*) from [question]";
                }
                OleDbCommand command2 = new OleDbCommand(commandTextcount, conn);
                int rows = (int)command2.ExecuteScalar();
                if (rows == 0)
                    return null;
                else
                {
                    OleDbCommand command = new OleDbCommand(commandText, conn);
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        question ret = new question();
                        ret.Question = reader["question"].ToString();
                        ret.A_answer = reader["A_answer"].ToString();
                        ret.B_answer = reader["B_answer"].ToString();
                        ret.C_answer = reader["C_answer"].ToString();
                        ret.D_answer = reader["D_answer"].ToString();
                        ret.Answer = reader["good_answer"].ToString();
                        ret.Question_id = (int)reader["ID"];
                        ret.Sender = (int)reader["sender"];
                        ret.Genre_id = (int)reader["genre"];
                        Questions.Add(ret.Question_id, ret);
                    }
                    conn.Close();
                    return Questions.Values;
                }
            }


        }

        public user getSender(int id)
        {
            if (id == null || id <= 0)
                throw new FaultException("Nincs megadva a lekérendő felhasználó id-ja!");

            user u = null;
            
            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT * FROM [user] WHERE [ID] = @userid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = id;

                OleDbDataReader reader = c.ExecuteReader();
                while (reader.Read())
                {
                    u = new user();
                    u.Id = id;
                    u.Username = reader["username"].ToString();
                    u.Password = reader["password"].ToString();
                    u.Firstname = reader["firstname"].ToString();
                    u.Lastname = reader["lastname"].ToString();
                    u.Emailaddress = reader["emailaddress"].ToString();

                    if (reader["admin"].ToString() == "0")
                        u.Admin = false;
                    else
                        u.Admin = true;

                }
                
            }

            return u;
        }

        public genre getGenre(int id)
        {
            genre g = null;

            if (id == null || id <= 0)
                throw new FaultException("Nincs megadva a lekérendő műfaj id-ja!");
            

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT * FROM [genre] WHERE [ID] = @genreid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("genreid", System.Data.DbType.Int32).Value = id;

                OleDbDataReader reader = c.ExecuteReader();
                while (reader.Read())
                {
                    g = new genre();
                    g.Genre_id = id;
                    g.Genre_name = reader["name"].ToString();
                }

            }

            return g;
        }

        public IEnumerable<genre> getAllGenres()
        {
            Genres.Clear();

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText;

                commandText = "SELECT * FROM [genre];";
                
                OleDbCommand command = new OleDbCommand(commandText, conn);
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    genre ret = new genre();
                    ret.Genre_id = (int)reader["ID"];                     
                    ret.Genre_name = reader["name"].ToString();
                    Genres.Add(ret.Genre_id, ret);
                }
                conn.Close();
                return Genres.Values;
            
                
            }
        }

        public bool isAdmin(user u)
        {
            if (u == null)
                throw new FaultException("Nem kaptam user típusú objektumot az adminlekérdezéshez!");
            if (u.Id == null || u.Id <= 0)
                throw new FaultException("Nincs megadva a felhasználó id-ja!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT admin FROM [user] WHERE [ID] = @userid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = u.Id;

                object res = c.ExecuteScalar();
                conn.Close();
                if (res.ToString() == "0")
                    return false;
                else
                    return true;
                
            }

        }

        public bool doAdmin(user u)
        {
            if (u == null)
                throw new FaultException("Nem kaptam user típusú objektumot az admincsináláshoz!");
            if (u.Id == null || u.Id <= 0)
                throw new FaultException("Nincs megadva a felhasználó id-ja!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "update [user] SET [admin] = 1 WHERE id = @userid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = u.Id;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool delAdmin(user u)
        {
            if (u == null)
                throw new FaultException("Nem kaptam user típusú objektumot az admintörléshez!");
            if (u.Id == null || u.Id <= 0)
                throw new FaultException("Nincs megadva a felhasználó id-ja!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "update [user] SET [admin] = 0 WHERE id = @userid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = u.Id;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool delUser(user u)
        {
            if (u == null)
                throw new FaultException("Nem kaptam user típusú objektumot a usertörléshez!");
            if (u.Id == null || u.Id <= 0)
                throw new FaultException("Nincs megadva a törlendő felhasználó id-ja!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "DELETE FROM [user] WHERE ID = @userid;";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = u.Id;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool delMatch(match m)
        {
            if (m == null)
                throw new FaultException("Nem kaptam match típusú objektumot a meccstörléshez!");
            if (m.Meccs_id == null || m.Meccs_id <= 0)
                throw new FaultException("Nincs megadva a törlendő meccs id-je!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "DELETE FROM [match] WHERE ID = @matchid;";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("matchid", System.Data.DbType.Int32).Value = m.Meccs_id;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool addMatch(match m)
        {
            if (m == null)
                throw new FaultException("Nem kaptam match típusú objektumot a meccshozzáadáshoz!");
            if (m.User_id == null || m.User_id <= 0)
                throw new FaultException("Nincs megadva felhasználóazonosító!");
            if (m.Points == null)
                throw new FaultException("Meg kell adni egy pontszámot, amit a felhasználó elért!");
            if (m.Questions == null)
                throw new FaultException("Nincs megadva, hogy hány kérdést kapott a felhasználó!");
            if (m.Questions < 0)
                throw new FaultException("Nem lehet a kérdések száma kevesebb nullánál!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "insert into [match] (user_id, points, questions)"
                + "values (@uid, @points, @questions);";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("sender", System.Data.DbType.Int32).Value = m.User_id;
                c.Parameters.Add("question", System.Data.DbType.Double).Value = m.Points;
                c.Parameters.Add("a_answer", System.Data.DbType.Int32).Value = m.Questions;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool delGenre(genre g)
        {
            if (g == null)
                throw new FaultException("Nem kaptam műfaj típusú objektumot a műfajtörléshez!");
            if (g.Genre_id == null || g.Genre_id <= 0)
                throw new FaultException("Nincs megadva a törlendő műfaj id-ja!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "DELETE FROM [genre] WHERE ID = @genreid;";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("genreid", System.Data.DbType.Int32).Value = g.Genre_id;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool modifyGenre(genre g)
        {
            if (g == null)
                throw new FaultException("Nem kaptam műfaj típusú objektumot a műfajmódosításhoz!");
            if (g.Genre_id == null || g.Genre_id <= 0)
                throw new FaultException("Nincs megadva a módosítandó műfaj id-je!");
            if (g.Genre_name == null || g.Genre_name.Count() == 0)
                throw new FaultException("Nincs megadva a módosítandó műfaj neve!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "update [genre] SET [name] = @uname WHERE [ID] = @genreid;";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add(new OleDbParameter("uname", g.Genre_name));
                c.Parameters.Add(new OleDbParameter("genreid", g.Genre_id));

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public bool addGenre(genre g)
        {
            if (g == null)
                throw new FaultException("Nem kaptam műfaj típusú objektumot a hozzáadáshoz!");
            if (g.Genre_name == null || g.Genre_name.Count() == 0)
                throw new FaultException("Nincs megadva a műfaj neve!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "insert into [genre] ([name])"
                + "values (@gname);";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("gname", System.Data.DbType.Int32).Value = g.Genre_name;

                if (c.ExecuteNonQuery() != 1)
                    return false;
                else
                    return true;
            }
        }

        public match getMatchById(int id)
        {
            match u = null;


            if (id == null || id <= 0)
                throw new FaultException("Nincs megadva a meccs lekérdezéséhez az id!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT * FROM [match] WHERE [ID] = @matchid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("matchid", System.Data.DbType.Int32).Value = id;

                OleDbDataReader reader = c.ExecuteReader();
                while (reader.Read())
                {
                    u = new match();
                    u.Meccs_id = id;
                    u.Points = (double)reader["points"];
                    u.Questions = (int)reader["questions"];
                    u.User_id = (int)reader["user_id"];
                }

            }

            return u;
        }

        public question getQuestionById(int id)
        {
            question u = null;


            if (id == null || id <= 0)
                throw new FaultException("Nincs megadva a kérdés lekérdezéséhez az id!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT * FROM [question] WHERE [ID] = @questionid";
                OleDbCommand c = new OleDbCommand(CommandText, conn);

                c.Parameters.Add("questionid", System.Data.DbType.Int32).Value = id;

                OleDbDataReader reader = c.ExecuteReader();
                while (reader.Read())
                {
                    u = new question();
                    u.Question_id = id;
                    u.Question = reader["question"].ToString();
                    u.Sender = (int)reader["sender"];
                    u.A_answer = reader["A_answer"].ToString();
                    u.B_answer = reader["B_answer"].ToString();
                    u.C_answer = reader["C_answer"].ToString();
                    u.D_answer = reader["D_answer"].ToString();
                    u.Answer = reader["good_answer"].ToString();
                    u.Genre_id = (int)reader["genre"];
                }

            }

            return u;
        }

        public IEnumerable<user> getAllUsers()
        {
            Users.Clear();

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText;

                commandText = "SELECT * FROM [user];";

                OleDbCommand command = new OleDbCommand(commandText, conn);
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    user ret = new user();
                    ret.Id = (int)reader["ID"];
                    ret.Username = reader["username"].ToString();
                    ret.Firstname = reader["firstname"].ToString();
                    ret.Lastname = reader["lastname"].ToString();
                    ret.Password = reader["password"].ToString();
                    ret.Emailaddress = reader["emailaddress"].ToString();
                    if (reader["admin"].ToString() == "0")
                        ret.Admin = false;
                    else
                        ret.Admin = true;
                    Users.Add(ret.Id, ret);
                }
                conn.Close();
                return Users.Values;
            }
        }

        public IEnumerable<match> getAlluserMatches(user r)
        {
            Matches.Clear();

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText;

                commandText = "SELECT * FROM [match] WHERE user_id = @userid;";

                OleDbCommand command = new OleDbCommand(commandText, conn);
                command.Parameters.Add("userid", System.Data.DbType.Int32).Value = r.Id;
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    match m = new match();
                    m.User_id = r.Id;
                    m.Questions = (int)reader["questions"];
                    m.Points = (double)reader["points"];
                    m.Meccs_id = (int)reader["ID"];
                    Matches.Add(m.Meccs_id, m);
                }
                conn.Close();
                return Matches.Values;
            }
        }

        public IEnumerable<match> getAllMatches()
        {
            Matches.Clear();

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText;

                commandText = "SELECT * FROM [match];";

                OleDbCommand command = new OleDbCommand(commandText, conn);
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    match m = new match();
                    m.User_id = (int)reader["user_id"];
                    m.Questions = (int)reader["questions"];
                    m.Points = (double)reader["points"];
                    m.Meccs_id = (int)reader["ID"];
                    Matches.Add(m.Meccs_id, m);
                }
                conn.Close();
                return Matches.Values;
            }
        }

        public double CountPoints(user r)
        {
            if (r == null)
                throw new FaultException("Nem adtál meg felhasználót! (null)");

            if (r.Id == null || r.Id <= 0)
                throw new FaultException("Nincs id megadva!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT Sum(points) FROM [match] WHERE user_id = @userid;";
                OleDbCommand c = new OleDbCommand(CommandText, conn);
                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = r.Id;

                object res = c.ExecuteScalar();
                
                if (res != DBNull.Value)
                    return (double)res;
                else
                    return 0;
            }
        }

        public int CountPlayedQuestions(user r)
        {
            if (r == null)
                throw new FaultException("Nem adtál meg felhasználót! (null)");

            if (r.Id == null || r.Id <= 0)
                throw new FaultException("Nincs id megadva!");

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string CommandText = "SELECT Sum(questions) FROM [match] WHERE user_id = @userid;";
                OleDbCommand c = new OleDbCommand(CommandText, conn);
                c.Parameters.Add("userid", System.Data.DbType.Int32).Value = r.Id;

                object res = c.ExecuteScalar();
                if (res != DBNull.Value)
                    return System.Convert.ToInt32(res);
                else
                    return 0;
            }
        }

        public IEnumerable<toplist> getToplist()
        {
            Toplists.Clear();

            using (OleDbConnection conn = new OleDbConnection(connect_url))
            {
                conn.Open();
                string commandText;

                commandText = "SELECT user_id, Sum(points) AS SumPoints FROM [match] GROUP BY user_id ORDER BY Sum(points) DESC;";

                OleDbCommand command = new OleDbCommand(commandText, conn);
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    toplist t = new toplist();
                    t.User = this.getSender(System.Convert.ToInt32(reader["user_id"]));
                    t.Points = (double)reader[1];
                    Toplists.Add(t.User.Id, t);
                }
                conn.Close();
                return Toplists.Values;
            }

        }

    }
}